package com.sidneysimmons.financially.adapters;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.sidneysimmons.financially.Account;
import com.sidneysimmons.financially.Category;
import com.sidneysimmons.financially.exceptions.DuplicateAccountException;
import com.sidneysimmons.financially.exceptions.DuplicateCategoryException;
import com.sidneysimmons.financially.utils.StringUtil;

public class DatabaseAdapter {
	private final String CLASS_NAME = this.getClass().getName();
	
	private Context context;
	private DatabaseHelp databaseHelp;
	private static SQLiteDatabase database;
	
	/* Database properties */
	private static final String DATABASE_NAME = "financiallyDatabase";
	private static final int DATABASE_VERSION = 5;
	
	/* Table names */
	public static final String TABLE_ACCOUNTS = "accounts";
	public static final String TABLE_CATEGORIES = "categories";
	
	/* Tables */
	private static final String ACCOUNTS_CREATE = "create table if not exists accounts (_id integer primary key autoincrement, "
			+ "title text not null);";
	private static final String CATEGORIES_CREATE = "create table if not exists categories (_id integer primary key autoincrement, "
			+ "account_id integer not null, title text not null, balance text not null default '0', percentage text, goal text);";
	
	public DatabaseAdapter(Context context) {
		this.context = context;
	}
	
	/**
	 * Open the database for writing.
	 * 
	 * @throws SQLException If the database cannot be opened for writing.
	 */
	private void open() throws SQLException {
		databaseHelp = new DatabaseHelp(context);
		database = databaseHelp.getWritableDatabase();
	}

	/**  Close the database. */
	private void close() {
		databaseHelp.close();
	}

	/**  SQL drop table  */
	private void dropTable(String table) {
		database.execSQL("DROP TABLE IF EXISTS " + table);
	}
	
	/**  Drop all the tables  */
	private void dropAllTables() {
		dropTable(TABLE_CATEGORIES);
		dropTable(TABLE_ACCOUNTS);
	}
	
	/**  Create all the tables  */
	private void createAllTables() {
		database.execSQL(ACCOUNTS_CREATE);
		database.execSQL(CATEGORIES_CREATE);
	}
	
	/**
	 * Erase the data in these tables:<br><i>
	 * categories<br>
	 * accounts</i>
	 */
	public void eraseData() {
		open();
		dropAllTables();
		createAllTables();
		close();
	}
	
	/**
	 * Add an account to the device's database.
	 * 
	 * @param account - The Account to add.
	 * 
	 * @return true if successful, false otherwise
	 */
	public boolean addAccount(Account account) throws DuplicateAccountException {
		// Return if account is null or title is blank/null
		if (account == null || StringUtil.isBlankOrNull(account.getTitle())) {
			Log.d(CLASS_NAME, "Can't add account with blank/null account or account title.  Returning false.");
			return false;
		}
		
		open();
		database.execSQL(ACCOUNTS_CREATE);
		
		// Check for duplicates
		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"title"}, "title=\""+account.getTitle()+"\"", null, null, null, null);
		if (c.getCount() > 0) {
			c.close();
			close();
			
			Log.d(CLASS_NAME, "Can't add duplicate account.  Throwing DuplicateAccountException.");
			throw new DuplicateAccountException();
		} else {
			c.close();
		}

		ContentValues initialValues = new ContentValues();
		initialValues.put("title", account.getTitle());

		// Add the account
		database.insert(TABLE_ACCOUNTS, null, initialValues);
		close();
		
		return true;
	}

	/**
	 * Delete an account from the device database.
	 * 
	 * @param account - The account to delete
	 * 
	 * @return true if successful, false otherwise
	 */
	public boolean deleteAccount(Account account) {
		// Return if account is null
		if (account == null) {
			Log.d(CLASS_NAME, "account == " + account);
			Log.d(CLASS_NAME, "Can't delete account that is null.  Returning false.");
			return false;
		}

		// Delete all categories and other data tied to this account
		// Get row id of the account
		open();
		database.execSQL(ACCOUNTS_CREATE);

		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"_id"}, "title=\""+account.getTitle()+"\"", null, null, null, null);
		if (c.getCount() == 0) {
			Log.d(CLASS_NAME, "Can't delete an account from the database that doesn't exist.  Returning false.");
			c.close();
			close();
			return false;
		}

		c.moveToFirst();
		int rowId = c.getInt(0);
		c.close();
		
		// Delete all categories that are tied to this account
		database.execSQL(CATEGORIES_CREATE);
		database.delete(TABLE_CATEGORIES, "account_id=" + rowId, null);

		// Delete the account, create the table if it doesn't already exist
		database.execSQL(ACCOUNTS_CREATE);
		database.delete(TABLE_ACCOUNTS, "title=\""+account.getTitle()+"\"", null);
		close();
		
		return true;
	}

	/**
	 * Update an account in the device's database.
	 * 
	 * @param originalAccount - The Account to update.
	 * @param updatedAccount - The new Account.
	 * 
	 * @return true if successful, false otherwise
	 */
	public boolean updateAccount(Account originalAccount, Account updatedAccount) throws DuplicateAccountException {
		// Return if either are null
		if (originalAccount == null || updatedAccount == null) {
			Log.d(CLASS_NAME, "originalAccount == " + originalAccount);
			Log.d("CLASS_NAME", "updatedAccount == " + updatedAccount);
			Log.d(CLASS_NAME, "Can't update account with null original/updated account.  Returning false.");
			return false;
		}
		
		open();
		database.execSQL(ACCOUNTS_CREATE);
		Cursor c;

		// Check if the updated account title is different than the original
		// If so, we have to check if there is another account with the updated title
		if (!updatedAccount.getTitle().equals(originalAccount.getTitle())) {

			c = database.query(TABLE_ACCOUNTS, new String[] {"title"}, "title=\""+updatedAccount.getTitle()+"\"", null, null, null, null);
			if (c.getCount() > 0) {
				c.close();
				close();

				Log.d(CLASS_NAME, "Can't change an existing account title to one that is already used.  " +
						"Throwing DuplicateAccountException.");
				throw new DuplicateAccountException();
			} else {
				c.close();
			}
		}

		ContentValues values = new ContentValues();
		values.put("title", updatedAccount.getTitle());

		// Update the account, create the table if it doesn't already exist
		database.execSQL(ACCOUNTS_CREATE);
		database.update(TABLE_ACCOUNTS, values, "title=\""+originalAccount.getTitle()+"\"", null);
		close();
		
		return true;
	}

	/**
	 * Get all the accounts. Returns empty if none exist.
	 * 
	 * @return An ArrayList of accounts.
	 */
	public ArrayList<Account> getAccounts() {
		open();

		// Create the table if it doesn't already exist
		database.execSQL(ACCOUNTS_CREATE);

		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"title"}, null, null, null, null, null);
		ArrayList<Account> accounts = new ArrayList<Account>();

		c.moveToFirst();
		for (int i = 0; i < c.getCount(); i++) {
			Account account = new Account();
			account.setTitle(c.getString(0));

			accounts.add(account);
			c.moveToNext();
		}
		c.close();
		close();
		return accounts;
	}

	/**
	 * Add a category to the device's database.<br><br>
	 * 
	 * Skipped if category.title is null or blank.<br>
	 * 
	 * @param category - The Category to add.
	 * 
	 * @return true if successful, false otherwise
	 */
	public boolean addCategory(String accountName, Category category) throws DuplicateCategoryException {
		// Return if category is null or title is blank/null
		if (category == null || StringUtil.isBlankOrNull(category.getTitle())) {
			Log.d(CLASS_NAME, "Can't add category with blank/null category and/or category title.  Returning false.");
			return false;
		}
		
		if (StringUtil.isBlankOrNull(accountName)) {
			Log.d(CLASS_NAME, "Can't add category to a blank/null accountName.  Returning false.");
			return false;
		}
		
		// Get row id of the account
		open();
		database.execSQL(ACCOUNTS_CREATE);
		
		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"_id"}, "title=\""+accountName+"\"", null, null, null, null);
		if (c.getCount() == 0) {
			Log.d(CLASS_NAME, "Can't add category to an account name that doesn't exist.  Returning false.");
			c.close();
			close();
			return false;
		}
		
		c.moveToFirst();
		int rowId = c.getInt(0);
		c.close();
		
		database.execSQL(CATEGORIES_CREATE);
		
		// Check for duplicates
		c = database.query(TABLE_CATEGORIES, new String[] {"title"}, "account_id=" + rowId + " AND title=\""+category.getTitle()+"\"", null, null, null, null);
		if (c.getCount() > 0) {
			c.close();
			close();
			
			Log.d(CLASS_NAME, "Can't add duplicate category.  Throwing DuplicateCategoryException.");
			throw new DuplicateCategoryException();
		} else {
			c.close();
		}
		
		ContentValues initialValues = new ContentValues();
		
		// Leave the balance out if blank/null.  To let the default value be set.
		if (category.getBalance() != null && category.getBalance().length() != 0)
			initialValues.put("balance", category.getBalance());
		
		initialValues.put("account_id", rowId);
		initialValues.put("title", category.getTitle());
		initialValues.put("percentage", category.getPercentage());
		initialValues.put("goal", category.getGoal());

		// Add the category
		database.insert(TABLE_CATEGORIES, null, initialValues);
		close();
		
		return true;
	}
	
	/**
	 * Deletes the category from the device's database.
	 * 
	 * @param categoryTitle
	 * 
	 * @return true if successful, false otherwise
	 */
	public boolean deleteCategory(String accountName, String categoryTitle) {
		// Return if categoryTitle is blank/null
		if (StringUtil.isBlankOrNull(categoryTitle)) {
			Log.d(CLASS_NAME, "Can't delete category with blank/null categoryTitle.  Returning false.");
			return false;
		}
		
		if (StringUtil.isBlankOrNull(accountName)) {
			Log.d(CLASS_NAME, "Can't delete category with blank/null accountName.  Returning false.");
			return false;
		}

		// Get row id of the account
		open();
		database.execSQL(ACCOUNTS_CREATE);

		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"_id"}, "title=\""+accountName+"\"", null, null, null, null);
		if (c.getCount() == 0) {
			Log.d(CLASS_NAME, "Can't update category for an accountName that doesn't exist.  Returning false.");
			c.close();
			close();
			return false;
		}

		c.moveToFirst();
		int rowId = c.getInt(0);
		c.close();

		// Delete the category, create the table if it doesn't already exist
		database.execSQL(CATEGORIES_CREATE);
		database.delete(TABLE_CATEGORIES, "account_id=" + rowId + " AND title=\""+categoryTitle+"\"", null);
		close();
		
		return true;
	}
	
	/**
	 * Update a category in the device's database.
	 * 
	 * @param accountName - The name of the Account the category is in.
	 * @param originalCategory - The original Category.
	 * @param updatedCategory - The updatedCategory.
	 * 
	 * @return true if successful, false otherwise
	 * @throws DuplicateCategoryException 
	 */
	public boolean updateCategory(String accountName, Category originalCategory, Category updatedCategory) throws DuplicateCategoryException {
		// Return if categoryTitle or newCategory are blank/null
		if (StringUtil.isBlankOrNull(originalCategory.getTitle()) || updatedCategory == null) {
			Log.d(CLASS_NAME, "Can't update category for blank/null categoryTitle and/or updatedCategory.  Returning false.");
			return false;
		}
		
		if (StringUtil.isBlankOrNull(accountName)) {
			Log.d(CLASS_NAME, "Can't update category for blank/null accountName.  Returning false.");
			return false;
		}
		
		// Get row id of the account
		open();
		database.execSQL(ACCOUNTS_CREATE);
		
		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"_id"}, "title=\""+accountName+"\"", null, null, null, null);
		if (c.getCount() == 0) {
			Log.d(CLASS_NAME, "Can't update category for an accountName that doesn't exist.  Returning false.");
			c.close();
			close();
			return false;
		}

		c.moveToFirst();
		int rowId = c.getInt(0);
		c.close();

		// Check if the updated category title is different than the original
		// If so, we have to check if there is another category with the updated title
		if (!updatedCategory.getTitle().equals(originalCategory.getTitle())) {
			database.execSQL(CATEGORIES_CREATE);

			c = database.query(TABLE_CATEGORIES, new String[] {"title"}, "account_id=" + rowId + " AND title=\""+updatedCategory.getTitle()+"\"", null, null, null, null);
			if (c.getCount() > 0) {
				c.close();
				close();

				Log.d(CLASS_NAME, "Can't change an existing category title to one that is already used.  " +
						"Throwing DuplicateCategoryException.");
				throw new DuplicateCategoryException();
			} else {
				c.close();
			}
		}

		ContentValues values = new ContentValues();
		values.put("title", updatedCategory.getTitle());
		values.put("percentage", updatedCategory.getPercentage());
		values.put("goal", updatedCategory.getGoal());

		// Update the category, create the table if it doesn't already exist
		database.execSQL(CATEGORIES_CREATE);
		database.update(TABLE_CATEGORIES, values, "account_id=" + rowId + " AND title=\""+originalCategory.getTitle()+"\"", null);
		close();
		
		return true;
	}
	
	/**
	 * Get all the categories. Returns empty if none exist.
	 * 
	 * @return An ArrayList of categories.
	 */
	public ArrayList<Category> getCategories(String accountName) {
		ArrayList<Category> categories = new ArrayList<Category>();
		
		if (StringUtil.isBlankOrNull(accountName)) {
			Log.d(CLASS_NAME, "Can't get categories for blank/null accountName.  Returning blank arraylist.");
			return categories;
		}
		
		// Get row id of the account
		open();
		database.execSQL(ACCOUNTS_CREATE);
		
		Cursor c = database.query(TABLE_ACCOUNTS, new String[] {"_id"}, "title=\""+accountName+"\"", null, null, null, null);
		if (c.getCount() == 0) {
			Log.d(CLASS_NAME, "Can't get categories for an accountName that doesn't exist.  Returning blank arraylist.");
			c.close();
			close();
			return categories;
		}
		
		c.moveToFirst();
		int rowId = c.getInt(0);
		c.close();
		
		// Create the table if it doesn't already exist
		database.execSQL(CATEGORIES_CREATE);
		
		c = database.query(TABLE_CATEGORIES, new String[] {"title", "balance", "percentage", "goal"}, "account_id="+rowId, null, null, null, null);
		
		c.moveToFirst();
		for (int i = 0; i < c.getCount(); i++) {
			Category category = new Category();
			category.setTitle(c.getString(0));
			category.setBalance(c.getString(1));
			category.setPercentage(c.getString(2));
			category.setGoal(c.getString(3));
			
			categories.add(category);
			c.moveToNext();
		}
		c.close();
		close();
		return categories;
	}
	
	/**  This is the helper class to deal with updating  */
	private static class DatabaseHelp extends SQLiteOpenHelper {
		DatabaseHelp(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(ACCOUNTS_CREATE);
			db.execSQL(CATEGORIES_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
			
			onCreate(db);
		}
	}
}